import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/dataquery/shipCertQuery.action?act=nation&certNationQO.org_type=10&certNationQO.org_code=140000&certNationQO.approval_date_startStr=2024-10-01&certNationQO.approval_date_endStr=2024-12-31&page=1&rows=10'
headers = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'WD8So6oV5GZ1nwtHLhPrIiPWa7WQl6%2F2GEZUpKK4n5anhmK79c89XBlxu2y%2FDrRfFEzD%2F9B9pY0LLwfY9OJ1gPDbnh6OPHjwXv0yspiye1lqANpSNgRQkIKAC8bokB%2FkrmpL7QkdfswQvQ%2FrIx09gyoR5EZedOjiYOyec9ihgQ05dksMbea6GZj8uCHrnQhMxMV1Wl%2FhIbPMlOExGare62Pdto9vpIpjeEmLibLU6fB4J75lQpGM6EKF1y6ocLAhqmk%2FkZJsh7B3RGobsUeWtnbWUUIIfqEOPrW06wKHzyG93ub2hvK8ZFdqbygEdgF1mthh4xsw9qdNI5BnoWPWgai50quXQZdLfwzyfChJQsuGtmh8UoFgu6lN1Rrv8vJOHxHIauEzTOgnJfCoL8Z%2FNb92%2Bz1FgKFg2xJQQ6upavBzexEYCgLb9nyrO21HMxAUtwDyG1AV1%2BouY3yIpfESdsiAKjS1155twMjb86S4nLk%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'OaEEg9NRYauAxe3mPAEPZ4grLAWuYIejoalcZpBPWlR1jp3yzB_N!-1812561480!1739537503057'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
strData = json.loads(response_lo.text)


if response_lo.status_code == 200:
    # 获取接口数据
    #html_content = response_lo.text
    #data = json.loads(response_lo.text)
    #print(">>>"+html_content)
    # 接口数据转换成json
    #data = json.loads(html_content)
    # 截取需要保存的数据
    #dataValue = data['datas']['table']['row']['table']['row']
    #print(data[1])

    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_register_nationality_ship'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)

                # value值是json的字段无法直接存入库，要转成string
                json_approval_date = record['approval_date']
                json_create_time = record['create_time']
                json_effective_date = record['effective_date']
                json_expire_date = record['expire_date']
                json_operate_time = record['operate_time']

                # json对象转成string后重新赋值回原来的字段
                str_approval_date = json.dumps(json_approval_date)
                str_create_time = json.dumps(json_create_time)
                str_effective_date = json.dumps(json_effective_date)
                str_expire_date = json.dumps(json_expire_date)
                str_operate_time = json.dumps(json_operate_time)

                record['approval_date'] = str_approval_date
                record['create_time'] = str_create_time
                record['effective_date'] = str_effective_date
                record['expire_date'] = str_expire_date
                record['operate_time'] = str_operate_time

                names = record['approval_date']
                print(names)
                # print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()